#include "datahandle.h"
#include "data.h"

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QDebug>

extern QString g_strAppPath;

DataHandle* DataHandle::s_pInstance = NULL;
DataHandle* DataHandle::getInstance()
{
    if(!s_pInstance)
    {
        s_pInstance = new DataHandle;
    }
    return s_pInstance;
}

bool DataHandle::init()
{
    QSqlDatabase db = QSqlDatabase::database(DataBase::strLocalVehDBConnect);
    if(db.isValid())
    {
        db.removeDatabase(DataBase::strLocalVehDBConnect);
    }
    db = QSqlDatabase::addDatabase("QSQLITE",DataBase::strLocalVehDBConnect);
    db.setDatabaseName(g_strAppPath + "/data/" + DataBase::strLocalVehDBName);
//    db.open();
    if(db.open())
    {
        QSqlQuery query(db);
        if(!query.exec(DataBase::VehTbl::strVehVipTbl))
        {
            qDebug() << QObject::tr("sql语句[%1]执行错误，错误原因[%2]")
                        .arg(DataBase::VehTbl::strVehVipTbl).arg(query.lastError().text());
            return false;
        }
        if(!query.exec(DataBase::VehTbl::strVehInnerTbl))
        {
            qDebug() << QObject::tr("sql语句[%1]执行错误，错误原因[%2]")
                        .arg(DataBase::VehTbl::strVehInnerTbl).arg(query.lastError().text());
            return false;
        }
    }
    else
    {
        qDebug() << QObject::tr("数据库[%1]打开失败[%2]")
                    .arg(DataBase::strLocalVehDBName)
                    .arg(db.lastError().text());
        return false;
    }
    return true;
}
// 处理月卡车辆表
bool DataHandle::AddMonthVehRecord(const MonthAndInnerCardInfo& info)
{
   QString strSql = QObject::tr("INSERT INTO veh_vip_tbl VALUES "
                       "('%1','%2','%3','%4','%5','%6','%7','%8')")
           .arg(info.operator_name)
           .arg(info.veh_plate)
           .arg(info.veh_ownner)
           .arg(info.veh_ownner_tel)
           .arg(info.veh_valid_start_time)
           .arg(info.veh_valid_end_time)
           .arg(info.veh_operate_time)
           .arg(info.veh_vip_charge_money);
   QSqlQuery query(QSqlDatabase::database(DataBase::strLocalVehDBConnect));
   if(!query.exec(strSql))
   {
       if(query.lastError().text().contains("UNIQUE constraint failed"))
       {
           emit sigMsg("表中已经存在现有项");
           return false;
       }
       emit sigMsg(QObject::tr("添加月卡操作失败:sql语句[%1],error原因[%2]").arg(strSql).arg(query.lastError().text()));
       return false;
   }
   return true;
}

bool DataHandle::DeleteMonthVehRecord(QString strPlate,QString strOwnner)
{
    QSqlQuery query(QSqlDatabase::database(DataBase::strLocalVehDBConnect));
    QString strSql;
    strSql.clear();
    if(!strPlate.isEmpty() && !strOwnner.isEmpty())
    {
       strSql = QObject::tr("DELETE FROM veh_vip_tbl WHERE veh_plate = '%1' AND veh_ownner = '%2' ")
                .arg(strPlate).arg(strOwnner);
    }
    if(!strPlate.isEmpty() && strOwnner.isEmpty())
    {
       strSql = QObject::tr("DELETE FROM veh_vip_tbl WHERE veh_plate = '%1' ")
                     .arg(strPlate);
    }
    if(strPlate.isEmpty() && !strOwnner.isEmpty())
    {
       strSql = QObject::tr("DELETE FROM veh_vip_tbl WHERE veh_ownner = '%1' ")
                     .arg(strOwnner);          
    }
    if(!query.exec(strSql))
    {
         QString strErr = QObject::tr("删除月卡操作失败:sql语句[%1],error原因[%2]").arg(strSql).arg(query.lastError().text());
         emit sigMsg(strErr);
         return false;
    }
    return true;
}

bool DataHandle::ModifyMonthVehRecord(const MonthAndInnerCardInfo& info)
{
    QString strSql = QObject::tr("UPDATE veh_vip_tbl SET operator_name = '%1',veh_ownner_tel = '%2',veh_valid_start_time = '%3',"
                                 "veh_valid_end_time = '%4', veh_operate_time='%5' WHERE "
                                 "veh_plate='%6' AND veh_ownner = '%7'")
            .arg(info.operator_name)
            .arg(info.veh_ownner_tel)
            .arg(info.veh_valid_start_time)
            .arg(info.veh_valid_end_time)
            .arg(info.veh_operate_time)
            .arg(info.veh_plate)
            .arg(info.veh_ownner);
    QSqlQuery query(QSqlDatabase::database(DataBase::strLocalVehDBConnect));
    if(!query.exec(strSql))
    {
        emit sigMsg(QObject::tr("修改月卡操作失败:sql语句[%1],error原因[%2]").arg(strSql).arg(query.lastError().text()));
        return false;
    }
    return true;
}

bool DataHandle::SelectMonthVehRecord(QSqlQuery& result,QString strPlate,QString strOwnner)
{
    QSqlQuery query(QSqlDatabase::database(DataBase::strLocalVehDBConnect));
    QString strSql;
    if(!strPlate.isEmpty() && !strOwnner.isEmpty())
    {
        strSql = QObject::tr("SELECT * FROM veh_vip_tbl WHERE veh_plate = '%1' AND veh_ownner = '%2' ")
                        .arg(strPlate).arg(strOwnner);
    }
    if(!strPlate.isEmpty() && strOwnner.isEmpty())
    {
        strSql = QObject::tr("SELECT *  FROM veh_vip_tbl WHERE veh_plate LIKE '%%1%' ")
                    .arg(strPlate);
    }
    if(strPlate.isEmpty() && !strOwnner.isEmpty())
    {
        strSql = QObject::tr("SELECT *  FROM veh_vip_tbl WHERE veh_ownner LIKE '%%1%' ")
                        .arg(strOwnner);
    }
    if(!query.exec(strSql))
    {
          emit sigMsg(QObject::tr("查找月卡操作失败:sql语句[%1],error原因[%2]").arg(strSql).arg(query.lastError().text()));
          return false;
    }
    result = query;
    return true;
}

// 处理期卡(内部)车辆表
bool DataHandle::AddOfCardVehRecord(const MonthAndInnerCardInfo& info)
{
    QString strSql = QObject::tr("INSERT INTO veh_inner_tbl VALUES "
                        "('%1','%2','%3','%4','%5','%6','%7')")
            .arg(info.operator_name)
            .arg(info.veh_plate)
            .arg(info.veh_ownner)
            .arg(info.veh_ownner_tel)
            .arg(info.veh_valid_start_time)
            .arg(info.veh_valid_end_time)
            .arg(info.veh_operate_time);
    QSqlQuery query(QSqlDatabase::database(DataBase::strLocalVehDBConnect));
    if(!query.exec(strSql))
    {
        if(query.lastError().text().contains("UNIQUE constraint failed"))
        {
            emit sigMsg("表中已经存在现有项");
            return false;
        }
        emit sigMsg(QObject::tr("添加期卡操作失败:sql语句[%1],error原因[%2]").arg(strSql).arg(query.lastError().text()));
        return false;
    }
    return true;
}

bool DataHandle::DeleteOfCardVehRecord(QString strPlate,QString strOwnner)
{
     QSqlQuery query(QSqlDatabase::database(DataBase::strLocalVehDBConnect));
     QString strSql;
     if(!strPlate.isEmpty() && !strOwnner.isEmpty())
     {
         strSql = QObject::tr("DELETE FROM veh_inner_tbl WHERE veh_plate = '%1' AND veh_ownner = '%2' ")
                    .arg(strPlate).arg(strOwnner);
     }
     if(!strPlate.isEmpty() && strOwnner.isEmpty())
     {
         strSql = QObject::tr("DELETE FROM veh_inner_tbl WHERE veh_plate = '%1' ")
                    .arg(strPlate);
     }
     if(strPlate.isEmpty() && !strOwnner.isEmpty())
     {
         strSql = QObject::tr("DELETE FROM veh_inner_tbl WHERE veh_ownner = '%1' ")
                    .arg(strOwnner);
     }
     if(!query.exec(strSql))
     {
        emit sigMsg(QObject::tr("删除期卡操作失败:sql语句[%1],error原因[%2]").arg(strSql).arg(query.lastError().text()));
        return false;
     }
     return true;
}

bool DataHandle::ModifyOfCardVehRecord(const MonthAndInnerCardInfo& info)
{
    QString strSql = QObject::tr("UPDATE veh_inner_tbl SET operator_name = '%1',veh_ownner_tel = '%2',veh_valid_start_time = '%3',"
                                 "veh_valid_end_time = '%4', veh_operate_time='%5' WHERE "
                                 "veh_plate='%6' AND veh_ownner = '%7'")
            .arg(info.operator_name)
            .arg(info.veh_ownner_tel)
            .arg(info.veh_valid_start_time)
            .arg(info.veh_valid_end_time)
            .arg(info.veh_operate_time)
            .arg(info.veh_plate)
            .arg(info.veh_ownner);
    QSqlQuery query(QSqlDatabase::database(DataBase::strLocalVehDBConnect));
    if(!query.exec(strSql))
    {
        emit sigMsg(QObject::tr("修改期卡操作失败:sql语句[%1],error原因[%2]").arg(strSql).arg(query.lastError().text()));
        return false;
    }
    return true;
}

bool DataHandle::SelectOfCardVehRecord(QSqlQuery& result,QString strPlate,QString strOwnner)
{
    QSqlQuery query(QSqlDatabase::database(DataBase::strLocalVehDBConnect));
    QString strSql;
    if(!strPlate.isEmpty() && !strOwnner.isEmpty())
    {
        strSql = QObject::tr("SELECT * FROM veh_inner_tbl WHERE veh_plate = '%1' AND veh_ownner = '%2' ")
                    .arg(strPlate).arg(strOwnner);   
    }
    if(!strPlate.isEmpty() && strOwnner.isEmpty())
    {
        strSql = QObject::tr("SELECT *  FROM veh_inner_tbl WHERE veh_plate LIKE '%%1%' ")
                    .arg(strPlate);
    }
    if(strPlate.isEmpty() && !strOwnner.isEmpty())
    {
        strSql = QObject::tr("SELECT *  FROM veh_inner_tbl WHERE veh_ownner LIKE '%%1%' ")
                    .arg(strOwnner);         
    }
    if(!query.exec(strSql))
    {
        emit sigMsg(QObject::tr("查找期卡操作失败:sql语句[%1],error原因[%2]").arg(strSql).arg(query.lastError().text()));
        return false;
    }
    result = query;
    return true;
}
